package com.ht.pms.core.dao.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.ht.pms.core.common.constant.SalaryConstants;
import com.ht.pms.core.dao.SalaryBusinessDictDao;
import com.ht.pms.core.dto.dict.DictParamsDto;
import com.ht.pms.core.dto.dict.DictRecordBaseDto;
import com.ht.pms.core.dto.tempalte.TemplateReq;
import com.ht.pms.core.dto.upload.UploadFile;

@Service
public class SalaryBusinessDictDaoImpl implements SalaryBusinessDictDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public List<DictParamsDto> querySalaryBusinessDictTable() {
		String sql = "select * from t_dict_params";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<DictParamsDto>(DictParamsDto.class));
	}

	@Override
	public void addColumn(String tableName, String columnName, String dictDataDes) {
		String sql = "alter table " + tableName + " add " + columnName + " " + dictDataDes;
		jdbcTemplate.update(sql);
	}

	@Override
	public void insertBatchBusinessDict(List<Integer> paramIndex, String sql, List<List<String>> content,
			Integer fileId, String batchCode, int stateIndex) {

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, fileId);
				ps.setString(2, batchCode);
				ps.setInt(3, Integer.valueOf(content.get(i).get(stateIndex)));
				int index = 4;
				for (Integer dataIndex : paramIndex) {
					ps.setString(index, content.get(i).get(dataIndex));
					index++;
				}
			}

			@Override
			public int getBatchSize() {
				return content.size();
			}
		});
	}

	@Override
	public void deleteBusinessDictByBatch(Integer fileId) {
		String sqlBase = "delete from t_dict_record_base where file_id = ?";
		String sqlA = "delete from t_dict_record_a where file_id = ?";
		String sqlB = "delete from t_dict_record_b where file_id = ?";
		String sqlC = "delete from t_dict_record_c where file_id = ?";

		jdbcTemplate.update(sqlBase, fileId);
		jdbcTemplate.update(sqlA, fileId);
		jdbcTemplate.update(sqlB, fileId);
		jdbcTemplate.update(sqlC, fileId);
	}

	@Override
	public void updateBatchBusinessDict(List<Integer> paramIndex, String sql, List<List<String>> content,
			String batchCode, Integer fileId, int jobCodeIndex, int stateIndex) {

		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				int index = 1;
				for (Integer dataIndex : paramIndex) {
					ps.setString(index, content.get(i).get(dataIndex));
					index++;
				}
				
				ps.setInt(index, Integer.valueOf(content.get(i).get(stateIndex)));
				ps.setString(index + 1, batchCode);
				ps.setString(index + 2, content.get(i).get(jobCodeIndex));
				ps.setInt(index + 3, fileId);
			}

			@Override
			public int getBatchSize() {
				return content.size();
			}
		});

	}

	@Override
	public void insertBatchBusinessDictBase(Integer fileId, String batchCode, int stateIndex, int jobCodeIndex,
			List<List<String>> content) {

		String sql = "insert into t_dict_record_base(batch_code, file_id, in_state, audit_state, job_code) values(?, ?, ?, ?, ?)";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, batchCode);
				ps.setInt(2, fileId);
				ps.setInt(3, Integer.valueOf(content.get(i).get(stateIndex)));
				ps.setInt(4, SalaryConstants.SALARY_NO);
				ps.setString(5, content.get(i).get(jobCodeIndex));
			}

			@Override
			public int getBatchSize() {
				return content.size();
			}

		});

	}

	@Override
	public void updateBatchBusinessDictBase(Integer fileId, String batchCode, int stateIndex, int jobCodeIndex,
			List<List<String>> content) {
		String sql = "update t_dict_record_base set in_state = ?, file_id = ? where batch_code = ?  and job_code = ?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, Integer.valueOf(content.get(i).get(stateIndex)));
				ps.setInt(2, fileId);
				ps.setString(3, batchCode);
				ps.setString(4, content.get(i).get(jobCodeIndex));
			}

			@Override
			public int getBatchSize() {
				return content.size();
			}

		});

	}

	@Override
	public List<DictParamsDto> querySalaryBusinessDictExcludByTemplateCode(String templateCode) {
		String sql = "select a.* from t_dict_params a where a.dict_code not in(select dict_code from t_template_detail where template_code = ? )";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<DictParamsDto>(DictParamsDto.class), templateCode);
	}

	@Override
	public List<DictParamsDto> selectBatchDict(List<String> resultFields) {
		StringBuilder sql = new StringBuilder();
		for (String value : resultFields) {
			if (sql.length() == 0) {
				sql.append("select * from t_dict_params where dict_code in ('").append(value).append("'");
			} else {
				sql.append(",'").append(value).append("'");
			}
		}
		sql.append(")");
		return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<DictParamsDto>(DictParamsDto.class));
	}


	@Override
	public List<DictParamsDto> selectDictParamList(TemplateReq req) {
		StringBuilder sql = new StringBuilder("select * from t_dict_params where 1 = 1 ");
		List<Object> params = new ArrayList<>();
		if (StringUtils.isNotBlank(req.getKeyworld())) {
			sql.append(" and dict_name like ");
			sql.append("'%" + req.getKeyworld() + "%'");
		} 
		
		if(StringUtils.isNotBlank(req.getDictTableName())) {
			sql.append(" and dict_table_name = ? ");
			params.add(req.getDictTableName());
		}
		sql.append(" limit ? , ? ");
		params.add((req.getPage() - 1) * req.getLimit());
		params.add(req.getLimit());
		return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<DictParamsDto>(DictParamsDto.class), params.toArray());
	}

	@Override
	public int selectDictParamCount(TemplateReq req) {
		StringBuilder sql = new StringBuilder("select count(*) from t_dict_params where 1 = 1 ");
		List<Object> params = new ArrayList<>();
		if (StringUtils.isNotBlank(req.getKeyworld())) {
			sql.append(" and dict_name like ");
			sql.append("'%" + req.getKeyworld() + "%'");
		} 
		
		if(StringUtils.isNotBlank(req.getDictTableName())) {
			sql.append(" and dict_table_name = ? ");
			params.add(req.getDictTableName());
		}
		return jdbcTemplate.queryForObject(sql.toString(), Integer.class, params.toArray());

	}

	@Override
	public int updateDictParam(DictParamsDto req) {
		String sql = "update t_dict_params set dict_name = ? , dict_data_type = ? where dict_code = ?";

		return this.jdbcTemplate.update(sql, req.getDictName(), req.getDictDataType(), req.getDictCode());

	}

	@Override
	public DictParamsDto selectDictParamByDictCode(String dictCode) {
		String sql = "select * from t_dict_params where dict_code = ?";
		try {
			return this.jdbcTemplate.queryForObject(sql, new Object[] { dictCode },
					new BeanPropertyRowMapper<DictParamsDto>(DictParamsDto.class));
		} catch (EmptyResultDataAccessException e) {
			return null;
		}

	}

	@Override
	public DictParamsDto selectMaxDictParamByTableName(String salaryDictRecordC) {
		String sql = "SELECT a.* FROM `t_dict_params` a where  a.dict_column = (select max(dict_column) from t_dict_params where dict_table_name = a.dict_table_name) and a.dict_table_name = ? ";
		try {
			return this.jdbcTemplate.queryForObject(sql, new Object[] { salaryDictRecordC },
					new BeanPropertyRowMapper<DictParamsDto>(DictParamsDto.class));
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public int insertDictParam(DictParamsDto dto) {
		String sql = "insert into t_dict_params (dict_name, dict_code, dict_column, dict_data_type, dict_data_desc, dict_table_name, dict_service_type) values (?, ?, ?, ?, ?, ?, ?)";
		return this.jdbcTemplate.update(sql, dto.getDictName(), dto.getDictCode(), dto.getDictColumn(),
				dto.getDictDataType(), dto.getDictDataDesc(), dto.getDictTableName(), dto.getDictServiceType());
	}

	@Override
	public DictParamsDto selectDictParamByDictName(String dictName, String tableName) {
		String sql = "SELECT * FROM `t_dict_params` where  dict_name = ? and dict_table_name = ? ";
		try {
			return this.jdbcTemplate.queryForObject(sql, new Object[] { dictName, tableName },
					new BeanPropertyRowMapper<DictParamsDto>(DictParamsDto.class));
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public int createField(DictParamsDto dictParam) {
		String sql = "alter table t_dict_record_c add "+dictParam.getDictColumn()+" VARCHAR(45) COLLATE utf8_unicode_ci DEFAULT NULL ";
		return this.jdbcTemplate.update(sql);
	}

	@Override
	public DictRecordBaseDto selectDictRecordIsExist(String jobCode, Integer fileId, String tableName) {
		String sql = "select * from "+tableName+" where file_id = ? and job_code = ?";
	
		List<DictRecordBaseDto> list = jdbcTemplate.query(sql,new Object[] {fileId, jobCode}, new BeanPropertyRowMapper<DictRecordBaseDto>(DictRecordBaseDto.class));
		if(list == null || list.isEmpty()) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public int getExcelDataCount(String countSql) {
		return jdbcTemplate.queryForObject(countSql, Integer.class);
	}

	@Override
	public List<Map<String, Object>> getExcelDataLimit(String dataSql) {
		
		return jdbcTemplate.queryForList(dataSql);
	}

	@Override
	public int updateSalaryDictState(UploadFile uploadFile, String tableName) {
		String sql = "update "+tableName+" set in_state = ? where batch_code = ?";
		
		return this.jdbcTemplate.update(sql, uploadFile.getState(), uploadFile.getBatchCode());
	}

}
